create table users(
	usid int primary key,
	uname varchar2(100) not null,
	pwd varchar2(20) not null,
	role int
);

create table productInfo(
	pid int primary key,
	pname varchar2(100) not null,
	des varchar2(1000),
	startPrice number(10,2),
	savePrice number(10,2),
	beginDate date,
	endDate date,
	pic varchar2(200)
);

insert into productInfo values(seq_productInfo_pid.nextval,'西游记','很好看哦',100000,100000,to_date('2016-03-18','yyyy-mm-dd'),to_date('2016-04-19','yyyy-mm-dd'),'../pics/1.gif');
insert into productInfo values(seq_productInfo_pid.nextval,'红楼梦','很好看哦',100000,100000,to_date('2016-04-01','yyyy-mm-dd'),to_date('2016-05-01','yyyy-mm-dd'),'../pics/1.gif');
insert into productInfo values(seq_productInfo_pid.nextval,'三国演义','很好看哦',100000,100000,to_date('2016-03-18','yyyy-mm-dd'),to_date('2016-04-19','yyyy-mm-dd'),'../pics/1.gif');
insert into productInfo values(seq_productInfo_pid.nextval,'水浒传','很好看哦',100000,100000,to_date('2016-05-01','yyyy-mm-dd'),to_date('2016-06-01','yyyy-mm-dd'),'../pics/1.gif');


create table bidInfo(
	bid int primary key,
	usid int
		constraint FK_bidInfo_users_usid references users(usid),
	pid int
		constraint FK_bidInfo_produceInfo_pid references productInfo(pid),
	createTime date,
	price number(10,2)	
);

drop table bidInfo;
drop table productInfo;


create sequence seq_users_usid start with 1001;
create sequence seq_productInfo_pid start with 1001;
create sequence seq_bidInfo_bid start with 1001;

select p.*,rownum from productInfo p;

select a.*,1 as flag from (select * from productInfo where beginDate<=sysdate and endDate>=sysdate order by endDate desc) a
union
select b.*,2 as flag  from (select * from productInfo where beginDate>sysdate order by beginDate asc) b

select * from users


















